/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package suministros.ejb30.DAO;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import javax.annotation.Resource;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.PersistenceUnit;
import javax.persistence.Query;
import javax.sql.DataSource;
import org.apache.commons.io.IOUtils;
import suministros.ejb30.TO.EncargoTO;
import suministros.ejb30.TO.ParametrosConsultaTO;
import suministros.ejb30.entity.Encargo;
import suministros.ejb30.helper.Utiles_Convertir;

/**
 *
 * @author Luis_Angel
 */
@Stateless
public class SumiGestionEncargosDAO implements SumiGestionEncargosDAOLocal {

     @PersistenceUnit(unitName="suministros-pu")
     EntityManagerFactory emf;

    EntityManager em;

     @Resource(name="jdbc/Administracion")
    DataSource dsAdministracion;

     @PostConstruct
      public void inicializaBean()
      {
        this.em = this.emf.createEntityManager();
      }

    @PreDestroy
      public void finalizaBean() {
        this.em.close();
      }

    public boolean insertarEncargo(Encargo encargo) throws Exception {
        boolean retorno = false;
         EntityTransaction tx = this.em.getTransaction();
          tx.begin();
           try {

               this.em.persist(encargo);
               this.em.flush();
                tx.commit();
              retorno = true;
            } catch (Exception e) {
                 tx.rollback();
              throw e;
            }
           return retorno;
    }

    public List<EncargoTO> consultarEncargo(ParametrosConsultaTO parametros,int pagActual, int pagTamanno) throws Exception {
            String cadena_where = "";
        if(parametros.getRuc_empresa()!=null)
            cadena_where+="where cp.cod_proveedor = '"+parametros.getRuc_proveedor()+ "' ";

        if(parametros.getAnio()!=null){
            if(cadena_where.length()==0)
                cadena_where+="where cp.ano = '"+parametros.getAnio()+ "' ";
            else
                cadena_where+="and cp.ano = '"+parametros.getAnio()+ "' ";
        }
        if(parametros.getMes()!=null){
            if(cadena_where.length()==0)
                cadena_where+="where cp.mes = '"+parametros.getMes()+ "' ";
            else
                cadena_where+="and cp.mes = '"+parametros.getMes()+ "' ";
        }
            
        Query consulta = this.em.createNativeQuery("select enc.id_encargo,emp.razon_social,CONVERT(VarChar(50), enc.fecha_inicio, 103),CONVERT(VarChar(50), enc.fecha_fin, 103),enc.ano,enc.mes,enc.observacion " +
                                                        "from empresa_encargo enc inner join   empresa emp on enc.id_empresa=emp.id_empresa "+
                                                             cadena_where + " order by enc.fecha_inicio desc");
        return Utiles_Convertir.convertirTOEncargoTO(consulta.setFirstResult(pagActual*pagTamanno).setMaxResults(pagTamanno).getResultList());
     }

     public byte[] getEncargoArchivo(int id_encargo) throws Exception {
         byte[] data=null;
        Connection cnx = null;
        try {
          //Utiles_Fecha fecha = new Utiles_Fecha(orden.getFechaemision());
          cnx = this.dsAdministracion.getConnection();
          PreparedStatement csLlamada = cnx.prepareStatement("select documento from empresa_encargo where id_encargo=" +id_encargo);
          ResultSet rs =csLlamada.executeQuery();

          while(rs.next()){
               InputStream is = rs.getBinaryStream(1);
               data = IOUtils.toByteArray(is);
              /* Filefile = new File(id_comprobante+".pdf");
               org.apache.commons.io.FileUtils.writeByteArrayToFile(file, data);*/
          }
        }
        catch (SQLException ex) {
          throw ex;
        } finally {
          try {
            cnx.close();
          } catch (Exception ex) {
            Logger.getLogger(SumiGestionPedidoDAO.class.getName()).log(Level.SEVERE, null, ex);
          }
        }
       return data;

    }

      public int getNumeroDeEncargos(ParametrosConsultaTO parametros) throws Exception {
         String cadena_where = "";
        if(parametros.getRuc_empresa()!=null)
            cadena_where+="where cp.cod_proveedor = '"+parametros.getRuc_proveedor()+ "' ";

        if(parametros.getAnio()!=null){
            if(cadena_where.length()==0)
                cadena_where+="where cp.ano = '"+parametros.getAnio()+ "' ";
            else
                cadena_where+="and cp.ano = '"+parametros.getAnio()+ "' ";
        }
        if(parametros.getMes()!=null){
            if(cadena_where.length()==0)
                cadena_where+="where cp.mes = '"+parametros.getMes()+ "' ";
            else
                cadena_where+="and cp.mes = '"+parametros.getMes()+ "' ";
        }

      Query consulta = this.em.createNativeQuery("select enc.id_encargo,emp.razon_social,CONVERT(VarChar(50), enc.fecha_inicio, 103),CONVERT(VarChar(50), enc.fecha_fin, 103),enc.ano,enc.mes,enc.observacion " +
                                                        "from empresa_encargo enc inner join   empresa emp on enc.id_empresa=emp.id_empresa "+
                                                             cadena_where + " order by enc.fecha_inicio desc");
        return consulta.getResultList().size();
    }


    // Add business logic below. (Right-click in editor and choose
    // "Insert Code > Add Business Method")
 
}
